﻿CREATE PROCEDURE [dbo].[proc_Company_Create]
	(
	 @CompanyName nvarchar(50),
	 @Startletter nvarchar(50),
	 @CompanyNumber nvarchar(50),
	 @CompanyPhone nvarchar(50),
	 @CompanyFax nvarchar(50),
	 @CompanyAddress nvarchar(100),
	 @Zip nvarchar(10),
	 @ContactPerson nvarchar(20),
	 @ContactPhone nvarchar(20),
	 @ContactEmail nvarchar(50),
	 @WebAddress nvarchar(50),
	 @Products nvarchar(2000),
	 @CompanyAllName nvarchar(50),
	 @Mid int,
	 @CompanyFR NVARCHAR(50),
	 @CreateDate datetime
	)
AS
	begin TRAN
	
--	declare @Mid_tem varchar(100)
--	declare @Mid_temp varchar(100)
--	BEGIN		
--	SET @Mid_tem=(SELECT top(1) dwbh FROM Company ORDER BY dwbh DESC)
--	
--	IF(@Mid_tem='' OR @Mid_tem IS NULL)				
--	SET @Mid_temp='01'		
--	ELSE 
--	BEGIN		
--		declare @Mid_tt varchar(100)
--		set @Mid_tt=CAST(CONVERT(int,SUBSTRING(@Mid_tem,LEN(@Mid_tem)-1,2)+1) AS VARCHAR(10))
--		
--		IF(LEN(@Mid_tt)=1)		
--			SET @Mid_temp='0'+@Mid_tt
--		ELSE 
--			SET @Mid_temp=@Mid_tt	
--	END	
--	END
		--Insert Into Company(CompanyName,Startletter,CompanyNumber,CompanyPhone,CompanyFax,CompanyAddress,Zip,ContactPerson,ContactPhone,ContactEmail,WebAddress,Products,CompanyAllName,Mid,dwbh,CompanyFR,CreateDate)
		--Values(@CompanyName,@Startletter,@CompanyNumber,@CompanyPhone,@CompanyFax,@CompanyAddress,@Zip,@ContactPerson,@ContactPhone,@ContactEmail,@WebAddress,@Products,@CompanyAllName,@Mid,'',@CompanyFR,@CreateDate)
	    
		----插入数据控制
		--Declare @CompanyId int,@Sort int
		--Select @CompanyId=@@Identity
		----Set @Sort=2
		----;WITH list As(
		----Select
		----Id,
		----@CompanyId As CompanyId,
		----UserName,
		----@Sort As Sort
		----From Company_RangeUsers Where IsAll=1
		----)
		----Insert Into Company_Range(ParentId,CompanyId,UserName,Sort) Select Id,CompanyId,UserName,Sort From list
	    
	 --   declare @Mid_temp varchar(100)
	 --   BEGIN
	 --   	IF(LEN(@CompanyId)=1)
	 --   	set @Mid_temp='0'+CAST(@CompanyId AS VARCHAR(10))
	 --   	ELSE
	 --       SET @Mid_temp=CAST(@CompanyId AS VARCHAR(10))
	 --       UPDATE Company SET dwbh = @Mid_temp WHERE Id=@CompanyId
	 --   END
		---- 徐福龙 2012 03 26 添加
		--Select isnull(@CompanyId,0) as I_ID
		
		----4.26 加更新管理员账号的数据权限--
		--UPDATE aspnet_Users
		--SET			Cid = Cid+'['+ CONVERT(VARCHAR, @CompanyId)+']'
		--	 where UserName ='admin'
		------------------GSJ----------------
		
		
		------------生成编号-------------
declare @TempSca table(row INT, cid int,cbh VARCHAR(10) )
INSERT INTO @TempSca(	row,	cid,	cbh) SELECT ROW_NUMBER() OVER (ORDER BY cid )AS Row, id,cid
   FROM Company c ORDER BY cid
   
   DECLARE @i INT,@count INT,@cbh VARCHAR(10),@nbh VARCHAR(10)
   DECLARE @a INT
   SET @count=(SELECT COUNT(1) FROM @TempSca)--单位总数
   SET @i=1
   SET @nbh=0
   WHILE @i<=@count
   BEGIN
   	SET @cbh=ISNULL((SELECT cbh FROM @TempSca WHERE row=@i),'0')-- 当前行的编号

   	IF @cbh<>'0'
   	BEGIN
   		DECLARE @a1 VARCHAR(10),@b1 VARCHAR(10)
   		SET @a1='0'
   		set @a1=convert(int,@cbh)+1
   		IF len(@a1)<2
   		BEGIN
   			SET @a1='0'+@a1
   		END   		   		 

   	SET @b1=ISNULL((SELECT top 1cbh FROM @TempSca WHERE cbh= @a1 ),'0')-- 
   	IF  @b1='0' --不存在编号
   	BEGIN   	   	
   		                	                    
   		SET @nbh=@a1   		
   		BREAK
   	END
   	END
   	ELSE
   		BEGIN
   			SET @nbh='0'+Convert(int,@cbh)+1   			
   			BREAK
   		END
   SET @i=@i+1	                                          
   END     
	    
------------生成编号-------------	
IF LEN(@nbh)>2
BEGIN
	RETURN
END

IF LEN(@nbh)<2
BEGIN
	SET @nbh='0'+@nbh
END	
		Insert Into Company(CompanyName,Startletter,CompanyNumber,CompanyPhone,CompanyFax,CompanyAddress,Zip,ContactPerson,ContactPhone,ContactEmail,WebAddress,Products,CompanyAllName,Mid,dwbh,CompanyFR,CreateDate,Cid)
		Values(@CompanyName,@Startletter,@CompanyNumber,@CompanyPhone,@CompanyFax,@CompanyAddress,@Zip,@ContactPerson,@ContactPhone,@ContactEmail,@WebAddress,@Products,@CompanyAllName,@Mid,'',@CompanyFR,@CreateDate,@nbh)
	    
	  
	    
		--插入数据控制
		Declare @CompanyId int,@Sort int
		Select @CompanyId=@@Identity
		
		--添加到组织架构中 2012.11.5 
		DECLARE @pid INT 
		SET @pid=(select id FROM zzjg z WHERE z.bh=(SELECT zid FROM ManagementArea ma WHERE ma.id= @Mid))
		EXEC [proc_zzjg_xx_CreateCompany] @CompanyId, @pid
		 
		--Set @Sort=2
		--;WITH list As(
		--Select
		--Id,
		--@CompanyId As CompanyId,
		--UserName,
		--@Sort As Sort
		--From Company_RangeUsers Where IsAll=1
		--)
		--Insert Into Company_Range(ParentId,CompanyId,UserName,Sort) Select Id,CompanyId,UserName,Sort From list
	    
	    
    
   
	    
	    declare @Mid_temp varchar(100)
	    BEGIN
	    	IF(LEN(@CompanyId)=1)
	    	set @Mid_temp='0'+CAST(@CompanyId AS VARCHAR(10))
	    	ELSE
	        SET @Mid_temp=CAST(@CompanyId AS VARCHAR(10))
	        UPDATE Company SET dwbh = @Mid_temp,cid=@nbh WHERE Id=@CompanyId
	    END
		-- 徐福龙 2012 03 26 添加
		Select isnull(@CompanyId,0) as I_ID
		

		
	commit tran
	RETURN
